﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Reflection;

namespace blackhouse.Data {
    [Obsolete("Zaleca się korzystanie z nowych klas dziedziczących po AdoDataHelper")]
    public class SqlDataHelper {

		#region Fields

		private string connectionString;
		private bool isInstaled = false;

		#endregion

		#region Properties

		/// <summary>
		/// Używana łącznie z InstalationScriptAssemblyFileFormat w celu identyfikowania w bazie rodzaju funkcjonalności
		/// </summary>
		public string Feature { get; set; }

		/// <summary>
		/// Używana łącznie z InstalationScriptAssemblyFileFormat w celu ładowania plików z skryptami
		/// </summary>
		public Type InstalationScriptAssembly { get; set; }

		/// <summary>
		/// ścieżka do skryptu instalacyjnego, np. blackhouse.SomeFeature.script_[x].sql - gdzie [x] oznacza wersję: 1,2,3 itd.
		/// </summary>
		public string InstalationScriptAssemblyFileFormat { get; set; }

		/// <summary>
		/// Używana łącznie z InstalationScriptAssemblyFileFormat w celu identyfikowania w bazie wersji funkcjonalności
		/// </summary>
		public int Version { get; set; }

		#endregion

		#region Constructors

		public SqlDataHelper(string sqlConnectionString) {
			this.connectionString = sqlConnectionString;
		}

		#endregion

		#region Methods

		private SqlCommand buildProcedureCommand(SqlConnection connection, string procedureName) {
			SqlCommand command = connection.CreateCommand();
			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = procedureName;
			return command;
		}

		private void checkInstalation() {
			if(isInstaled || string.IsNullOrEmpty(this.InstalationScriptAssemblyFileFormat)) {
				this.isInstaled = true;
				return;
			}
			isInstaled = true;  // najpierw ustawiamy flagę, że zainstalowano, ponieważ w trakcie instalacji jest wywoływana function Execute a ta wywołuje checkInstalation i tak w kółko

			int dbVersion = 0;
			try {
				dbVersion = this.ExecuteScalar<int>("bhp_GetVersion", r => Convert.ToInt32(r[0]),
					this.CreateParameter("@Feature", SqlDbType.NVarChar, this.Feature)
					);
			} catch {
				this.createVersioning();
				dbVersion = 0;
			}
			if(dbVersion != this.Version)
				this.install(dbVersion);
		}

        [Obsolete]
        public SqlParameter createParameter(string parameterName, SqlDbType dataType, object value) {
            return this.CreateParameter(parameterName, dataType, value);
        }

		public SqlParameter CreateParameter(string parameterName, SqlDbType dataType, object value) {
			return this.CreateParameter(parameterName, dataType, value, -1);
		}

        [Obsolete]
        public SqlParameter createParameter(string parameterName, SqlDbType dataType, object value, int size) {
            return this.CreateParameter(parameterName, dataType, value, size);
        }

		public SqlParameter CreateParameter(string parameterName, SqlDbType dataType, object value, int size) {
			SqlParameter parameter = new SqlParameter(parameterName, dataType);
			if(size > 0)
				parameter.Size = size;
			parameter.Value = value;
			return parameter;
		}

		private void createVersioning() {
			this.executeInstalationFile(typeof(SqlDataHelper).Assembly, "blackhouse.Data.versioning.sql");
		}

		public T[] Execute<T>(string commandName, ExecuteReader<T> reader) {
			return this.Execute<T>(commandName, reader, new SqlParameter[0]);
		}

		public T[] Execute<T>(string commandName, ExecuteReader<T> reader, params SqlParameter[] sqlParam) {

			this.checkInstalation();

			List<T> objs = new List<T>();

			try {
				using(SqlConnection connection = new SqlConnection(connectionString)) {
					connection.Open();
					SqlCommand command;
					command = buildProcedureCommand(connection, commandName);
					command.Parameters.AddRange(sqlParam);
					Stopwatch watch = new Stopwatch();
					watch.Start();
					SqlDataReader dbReader = command.ExecuteReader();
					watch.Stop();
					Trace.WriteLine(string.Format("Query:{0}\r\nparameters:{1}\r\nTime:{2} ms", commandName, string.Join("\r\n", sqlParam.Select<IDataParameter, string>(p => string.Format("{0}:{1}", p.ParameterName, p.Value)).ToArray()), watch.ElapsedMilliseconds));
					while(dbReader.Read())
						objs.Add(reader(dbReader));
					dbReader.Close();
					connection.Close();
				}
			} catch(Exception exc) {
				System.Diagnostics.Trace.Write(commandName);
				System.Diagnostics.Trace.Write(exc);
				System.Diagnostics.Debug.Write(commandName);
				System.Diagnostics.Debug.Write(exc);
				throw;
			}
			return objs.ToArray();
		}

		private void executeInstalationFile(int fileVersion) {
			this.executeInstalationFile(this.InstalationScriptAssembly.Assembly, this.InstalationScriptAssemblyFileFormat.Replace("[x]", fileVersion.ToString()));
		}

		private void executeInstalationFile(Assembly assembly, string file) {
			using(Stream st = assembly.GetManifestResourceStream(file)) {
				if(st != null) {
					using(StreamReader sr = new StreamReader(st)) {
						string sql = sr.ReadToEnd();
						using(SqlConnection connection = new SqlConnection(this.connectionString)) {
							connection.Open();
							string[] commandParts = sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
							foreach(string commandPart in commandParts) {
								SqlCommand command = connection.CreateCommand();
								command.CommandText = commandPart;
								command.ExecuteNonQuery();
							}
							connection.Close();
						}
					}
				}
			}
		}

		public int ExecuteNonQuery(string commandName, params SqlParameter[] sqlParam) {

			this.checkInstalation();

			int affectedRows = 0;
			try {
				using(SqlConnection connection = new SqlConnection(connectionString)) {
					connection.Open();
					SqlCommand command;
					command = buildProcedureCommand(connection, commandName);
					command.Parameters.AddRange(sqlParam);
					Stopwatch watch = new Stopwatch();
					watch.Start();
					affectedRows = command.ExecuteNonQuery();
					watch.Stop();
					Trace.WriteLine(string.Format("Query:{0}\r\nparameters:{1}\r\nTime:{2} ms", commandName, string.Join("\r\n", sqlParam.Select<IDataParameter, string>(p => string.Format("{0}:{1}", p.ParameterName, p.Value)).ToArray()), watch.ElapsedMilliseconds));
					connection.Close();
				}
			} catch(Exception exc) {
				System.Diagnostics.Trace.Write(commandName);
				System.Diagnostics.Trace.Write(exc);
				System.Diagnostics.Debug.Write(commandName);
				System.Diagnostics.Debug.Write(exc);
				throw;
			}
			return affectedRows;
		}

		public T ExecuteScalar<T>(string commandName, ExecuteReader<T> reader) {
			return this.ExecuteScalar<T>(commandName, reader, new SqlParameter[0]);
		}

		public T ExecuteScalar<T>(string commandName, ExecuteReader<T> reader, params SqlParameter[] sqlParam) {
			return this.Execute<T>(commandName, reader, sqlParam).FirstOrDefault();
		}

		private void install(int fromVersion) {
			for(int xv = fromVersion + 1; xv <= this.Version; xv++) {
				this.executeInstalationFile(xv);
				this.setVersion(xv);
			}
		}

		private void setVersion(int version) {
			this.ExecuteNonQuery("bhp_SetVersion",
				this.CreateParameter("@Feature", SqlDbType.NVarChar, this.Feature),
				this.CreateParameter("@Version", SqlDbType.Int, version)
				);
		}

		/// <summary>
		/// Za pomocą jednej metody możesz ustawić obsługę wersjonowania
		/// </summary>
		/// <param name="feature">Nazwa funkcjonalności, która ma podlegać wersjonowaniu</param>
		/// <param name="senderType">Klasa która obsługuje wersjonowanie.
		/// Może to być inna klasa z tego zestawu, gdyż parametr jest wykorzystywany do załadowania osadzonych plików sql</param>
		/// <param name="instalationScriptFileFormat">Format ścieżek do plików instalacyjnych, gdzie [x] oznacza numer wersji</param>
		/// <param name="version">aktualnie wykorzystywana wersja przez Business</param>
		public void SetVersioning(string feature, Type senderType, string instalationScriptFileFormat, int version) {
			this.Feature = feature;
			this.InstalationScriptAssembly = senderType;
			this.InstalationScriptAssemblyFileFormat = instalationScriptFileFormat;
			this.Version = version;
		}

		#endregion

		#region Delegates

		public delegate T ExecuteReader<T>(SqlDataReader reader);

		#endregion

	}
}
